#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   File Name：      operations.py
   Remark :         数据库操作
-------------------------------------------------
"""


import logging

from pymysql import DatabaseError

from hellchin.db.mysql.connection import MySqlConnection
from typing import Any, List, Tuple

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


class MySQLOperations:
    def __init__(self, pool: MySqlConnection):
        self.connection = pool.connect()

    def __del__(self):
        if self.connection:
            self.connection.close()
            logger.info("Connection closed in MySQLOperations destructor.")

    def execute(self, sql: str, params: Tuple[Any, ...] = None):
        """
        执行单条 SQL 语句（如 INSERT/UPDATE/DELETE）。

        :param sql: SQL 语句
        :param params: 参数
        :return: 受影响的行数
        """
        connection = self.connection

        try:
            with connection.cursor() as cursor:
                affected_rows = cursor.execute(sql, params)
                connection.commit()
                logger.info(f"Executed SQL: {sql}, Affected rows: {affected_rows}")
                return affected_rows
        except Exception as e:
            connection.rollback()   # 执行回滚
            logger.error(f"Error executing SQL: {sql}, Error: {e.args}")
            raise
        finally:
            logger.info("Finished executing SQL")
            connection.close()  # 归还连接池

    def fetch_one(self, sql: str, params: Tuple[Any, ...] = None) -> dict:
        """
        查询单条记录。
        :param sql: SQL 语句
        :param params: 参数
        :return: 查询结果（字典）
        """

        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params)
                result = cursor.fetchone()
                logger.info(f"Executed SQL: {sql}, Result: {result}")
                return result
        except DatabaseError as e:
            self.connection.rollback()
            logger.error(f"Error executing SQL: {sql}, Error: {e}")
            raise
        except Exception as e:
            logger.error(f"System error:{e}")
            raise



    def fetch_all(self, sql: str, params: Tuple[Any, ...] = None) -> List[dict]:
        """
        查询多条记录。

        :param sql: SQL 语句
        :param params: 参数
        :return: 查询结果（字典列表）
        """
        connection = self.connection
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql, params)
                results = cursor.fetchall()
                logger.info(f"Executed SQL: {sql}, Retrieved rows: {len(results)}")
                return results

        except Exception as e:
            logger.error(f"Error fetching all for SQL: {sql}, Error: {e}")
            raise


    def execute_many(self, sql: str, param_list: List[Tuple[Any, ...]]) -> int:
        """
        批量执行 SQL（如批量 INSERT）。

        :param sql: SQL 语句
        :param param_list: 参数列表
        :return: 受影响的行数
        """
        connection = self.connection()
        try:
            with connection.cursor() as cursor:
                affected_rows = cursor.executemany(sql, param_list)
                connection.commit()
                logger.info(f"Executed batch SQL: {sql}, Affected rows: {affected_rows}")
                return affected_rows
        except Exception as e:
            connection.rollback()
            logger.error(f"Error executing batch SQL: {sql}, Error: {e}")
            raise


    def execute_transaction(self, queries: List[Tuple[str, Tuple[Any, ...]]]) -> None:
        """
        执行事务。

        :param queries: SQL 语句和参数的列表
        """
        connection = self.connection()

        try:
            with connection.cursor() as cursor:
                for sql, params in queries:
                    cursor.execute(sql, params)
                connection.commit()
                logger.info(f"Transaction executed successfully")
        except Exception as e:
            connection.rollback()
            logger.error(f"Transaction failed: {e}")
            raise

if __name__ == '__main__':
    connection_pool = MySqlConnection()
    # mysql_operation = MySQLOperations(connection_pool)

"""
from mysql_tool import MySQLDBOperations, MySQLConnectionPool

# Initialize connection pool
connection_pool = MySQLConnectionPool(
    host="localhost",
    port=3306,
    username="root",
    password="password",
    database="test_db",
    use_pool=True  # Enable connection pool
)

# Initialize DB operations
db_operations = MySQLDBOperations(connection_pool)
"""
